# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import sqlite3
from urllib.request import urlretrieve
import os


# 存储管道
class SqlitePipeline(object):

    def __init__(self):
        self.con = sqlite3.connect('db.sqlite')
        self.cursor = self.con.cursor()

    def close_spider(self, spider):
        self.cursor.close()
        self.con.close()
        self.cursor = None
        self.con = None

    def process_item(self, item, spider):
        last_create_table_sql = ''
        insert_columns = []
        insert_values = []
        for key, value in item.items():
            if isinstance(value, int):
                last_create_table_sql += str(key) + ' INTEGER,'
            elif isinstance(value, str):
                last_create_table_sql += key + ' VARCHAR(255),'
            elif isinstance(value, float):
                last_create_table_sql += str(key) + ' FLOAT,'
            else:
                last_create_table_sql += str(key) + ' VARCHAR(255),'
            # insert_columns
            insert_columns.append(key)
            # insert_values
            insert_values.append(value)

        sql = f"""
            CREATE TABLE IF NOT EXISTS {spider.name} (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                {last_create_table_sql[:-1]}
            );
        """
        self.cursor.execute(sql)
        # 插入
        sql = f"""
            INSERT INTO {spider.name}({",".join(insert_columns)}) VALUES ({('?,'*len(insert_values))[:-1]});
        """
        self.cursor.execute(sql, insert_values)
        self.con.commit()
        return item


# 下载管道
class DownloaderPipeline(object):
    def process_item(self, item, spider):
        # 使用urlretrieve
        # 使用requests，以二进制形式写入文件
        # 使用系统自带的images下载管道

        # 文件路径
        item['img_url'] = item['img_url'][0]
        # 下载文件到指定路径
        os.makedirs("/".join(((item['img_uri'].split('/'))[:-1])), exist_ok=True)
        urlretrieve(item['img_url'], item['img_uri'])
        return item


from scrapy.pipelines.images import ImagesPipeline
# 继承ImagePipeline
import hashlib


class ImagePipeline(ImagesPipeline):
    # 1.修改存储的文件后缀
    # 2.获取对应的存储路径
    # 3.缩略图后缀集存储路径
    def file_path(self, request, response=None, info=None):
        # 图片url
        url = request.url
        # 获取文件后缀
        file_type = url.split(".")[-1]
        image_guid = hashlib.sha1(url.encode()).hexdigest()
        return f'full/%s.{file_type}' % (image_guid)

    # 重写item_completed(self, results, item, info)方法
    def item_completed(self, results, item, info):
        item['img_url'] = item['img_url'][0]

        # 返回item
        return item


# 文件下载
from scrapy.pipelines.files import FilesPipeline


class FilePipeline(FilesPipeline):
    """
    从scrapy中负责文件下载的files.py中找方法，重写不影响整个系统的函数，加上自己的功能，达到预期的效果。
    """
    def item_completed(self, results, item, info):
        item['img_url'] = item['img_url'][0]
        return item
